package org.bizming.medicine.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.bizming.base.service.SqlHelper;
import org.bizming.medicine.model.Medicine;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class MedicineMysqlDao implements MedicineDao {

	private Logger logger = LoggerFactory.getLogger(this.getClass());

	private JdbcTemplate jdbcTemplate;

	private SqlHelper sqlHelper;

	private String tableName = "medicine";

	@Override
	public int add(final Medicine t) {
		StringBuilder builder = new StringBuilder(100);
		builder.append("insert into ").append(getTableName());
		builder.append("(")
				.append("commonName,name,jixing,guige,company,approvalNumber,lotNumber,utilDate,unit,remainder");
		builder.append(")values(?,?,?,?,?,?,?,?,?,?)");

		final String sql = builder.toString();
		logger.info(sql);

		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, new String[] { "id" });// 返回id
				ps.setString(1, t.getCommonName());
				ps.setString(2, t.getName());
				ps.setString(3, t.getJixing());
				ps.setString(4, t.getGuige());
				ps.setString(5, t.getCompany());
				ps.setString(6, t.getApprovalNumber());
				ps.setString(7, t.getLotNumber());
				ps.setLong(8, t.getUtilDate());
				ps.setString(9, t.getUnit());
				ps.setInt(10, t.getRemainder());
				return ps;
			}

		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	@Override
	public Medicine get(int id) {

		String sql = sqlHelper.sqlGet(null, getTableName());
		logger.info(sql);

		Medicine t = jdbcTemplate.queryForObject(sql, new MedicineRowMapper(), id);

		return t;
	}

	@Override
	public int count() {
		String sql = sqlHelper.sqlCount(getTableName());
		logger.info(sql);

		int row = jdbcTemplate.queryForInt(sql);
		return row;
	}

	@Override
	public boolean remove(int id) {

		String sql = sqlHelper.sqlRemove(getTableName());
		logger.info(sql);

		int affectRow = jdbcTemplate.update(sql, id);
		if (affectRow > 0) {
			return true;
		} else {
			return false;
		}

	}

	@Override
	public List<Medicine> selectList() {

		String sql = sqlHelper.sqlSelectList(Medicine.class, getTableName());
		logger.info(sql);

		Object[] args = new Object[] { 0, 9999 };

		List<Medicine> rows = jdbcTemplate.query(sql, new MedicineRowMapper(), args);

		sqlHelper.clear(args);

		return rows;
	}

	@Override
	public boolean update(Medicine t) {
		List<Object> values = new ArrayList<Object>();
		String sql = sqlHelper.sqlUpdate(t, values, getTableName());
		logger.info(sql);

		values.add(t.getId());
		Object[] o = values.toArray();
		int affectRow = jdbcTemplate.update(sql, o);

		sqlHelper.clear(values);
		sqlHelper.clear(o);

		if (affectRow > 0) {
			return true;
		} else {
			return false;
		}
	}

	public boolean decreCount(int id, int count) {
		StringBuilder sb = new StringBuilder();
		sb.append("update ").append(getTableName()).append(" set remainder=remainder-? where id=?");
		Object[] objects = new Object[] { count, id };
		String sql = sb.toString();
		logger.info(sql);
		int row = jdbcTemplate.update(sql, objects);
		if (row == 1)
			return true;
		return false;
	}

	public boolean increCount(int id, int count) {
		StringBuilder sb = new StringBuilder();
		sb.append("update ").append(getTableName()).append(" set remainder=remainder+? where id=?");
		Object[] objects = new Object[] { count, id };
		String sql = sb.toString();
		logger.info(sql);
		int row = jdbcTemplate.update(sql, objects);
		if (row == 1)
			return true;
		return false;
	}

	class MedicineRowMapper implements RowMapper<Medicine> {

		@Override
		public Medicine mapRow(ResultSet rs, int rowNum) throws SQLException {
			Medicine t = new Medicine();
			t.setId(rs.getInt("id"));
			t.setCommonName(rs.getString("commonName"));
			t.setName(rs.getString("name"));
			t.setJixing(rs.getString("jixing"));
			t.setGuige(rs.getString("guige"));
			t.setCompany(rs.getString("company"));
			t.setApprovalNumber(rs.getString("approvalNumber"));
			t.setLotNumber(rs.getString("lotNumber"));
			t.setUtilDate(rs.getLong("utilDate"));
			t.setUnit(rs.getString("unit"));
			t.setRemainder(rs.getInt("remainder"));
			return t;
		}

	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public SqlHelper getSqlHelper() {
		return sqlHelper;
	}

	public void setSqlHelper(SqlHelper sqlHelper) {
		this.sqlHelper = sqlHelper;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

}
